package com.ruoyi.iot.service.impl;

import com.ruoyi.common.config.RuoYiConfig;
import com.ruoyi.common.exception.ServiceException;
import com.ruoyi.common.utils.StringUtils;
import com.ruoyi.iot.domain.IotQueryResult;
import com.ruoyi.iot.domain.entity.DeviceFaultWindow;
import com.ruoyi.iot.domain.entity.DeviceHealthScore;
import com.ruoyi.iot.domain.entity.jiuhetoudong.Product1714885465641558018DataJht;
import com.ruoyi.iot.domain.entity.suojianan.Product1714885465641558018DataSjn;
import com.ruoyi.iot.domain.enums.DeviceTimeScope;
import com.ruoyi.iot.mapper.DeviceFaultWindowMapper;
import com.ruoyi.iot.mapper.DeviceHealthScoreMapper;
import com.ruoyi.iot.service.HostDeviceMetricsService;
import com.ruoyi.iot.service.IotQueryService;
import com.ruoyi.iot.utils.Constants;
import io.swagger.v3.oas.annotations.media.Schema;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.MapUtils;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;

import java.math.BigDecimal;
import java.math.BigInteger;
import java.math.RoundingMode;
import java.lang.reflect.Field;
import java.lang.reflect.Modifier;
import java.sql.Timestamp;
import java.time.Duration;
import java.time.Instant;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.time.ZonedDateTime;
import java.time.format.DateTimeFormatter;
import java.time.format.DateTimeFormatterBuilder;
import java.time.format.DateTimeParseException;
import java.time.temporal.ChronoUnit;
import java.time.temporal.ChronoField;
import java.time.temporal.TemporalAdjusters;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.LinkedHashSet;
import java.util.List;
import java.util.Locale;
import java.util.Map;
import java.util.Set;
import java.util.TreeMap;
import java.util.stream.Collectors;

/**
 * 主机设备指标查询服务实现
 */
@Service
@Slf4j
public class HostDeviceMetricsServiceImpl implements HostDeviceMetricsService {

    private static final String DATA_TABLE = "product_1964935588177780737_data";
    private static final String HOST_DEVICE = "product_1714885465641558018_data";

    private static final DateTimeFormatter SQL_TIMESTAMP_FORMATTER =
            DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss.SSS");
    private static final DateTimeFormatter FLEXIBLE_LOCAL_DATE_TIME =
            new DateTimeFormatterBuilder()
                    .appendPattern("yyyy-MM-dd HH:mm:ss")
                    .optionalStart()
                    .appendFraction(ChronoField.MILLI_OF_SECOND, 1, 6, true)
                    .optionalEnd()
                    .toFormatter();
    private static final Map<String, String> FIELD_DESCRIPTION_MAP = buildFieldDescriptionMap();
    private static final String[] TEMPERATURE_FIELDS = {
            "astatortemp1", "bstatortemp1", "cstatortemp1",
            "astatortemp2", "bstatortemp2", "cstatortemp2",
            "nonshaftendtemp", "shaftendtemp"
    };
    private static final String TEMPERATURE_STATS_SELECT = buildTemperatureStatsSelectClause();
    private static final String STAT_LABEL_MAX = "最高温度";
    private static final String STAT_LABEL_MIN = "最低温度";
    private static final String STAT_LABEL_AVG = "平均温度";
    private static final Map<String, Field> FAULT_COUNT_FIELD_MAP = buildFaultCountFieldMap();
    private static final Map<String, String> FAULT_COUNT_DESCRIPTION_MAP = buildFaultCountDescriptionMap();
    //每个采样点之间的时间间隔是 0.0041667 小时（也就是 15 秒）
    private static final BigDecimal SAMPLE_INTERVAL_HOURS =
            BigDecimal.ONE.divide(BigDecimal.valueOf(240L), 12, RoundingMode.HALF_UP);
    private static final int ENERGY_SCALE = 4;
    private static final BigDecimal ZERO_HOURS =
            BigDecimal.ZERO.setScale(ENERGY_SCALE, RoundingMode.HALF_UP);
    private static final int SCORE_SCALE = 2;
    private static final BigDecimal ZERO_SCORE =
            BigDecimal.ZERO.setScale(SCORE_SCALE, RoundingMode.HALF_UP);
    private static final long MILLIS_PER_HOUR = 3_600_000L;

    private static final String AUTO_REPORT_METHOD = "自动上报";

    private final IotQueryService queryService;
    private final DeviceFaultWindowMapper faultWindowMapper;
    private final DeviceHealthScoreMapper healthScoreMapper;
    private final RuoYiConfig ruoYiConfig;

    public HostDeviceMetricsServiceImpl(IotQueryService queryService,
                                        DeviceFaultWindowMapper faultWindowMapper,
                                        DeviceHealthScoreMapper healthScoreMapper,
                                        RuoYiConfig ruoYiConfig) {
        this.queryService = queryService;
        this.faultWindowMapper = faultWindowMapper;
        this.healthScoreMapper = healthScoreMapper;
        this.ruoYiConfig = ruoYiConfig;
    }

    /**
     * 查询所有主机柜信息
     */
    @Override
    public List<Map<String, Object>> queryAllMainCabinetMetrics() {
        String sql = String.format(
                "select distinct a.device_id from product_1714885465641558018_data a");
        IotQueryResult query = query(sql);
        if (query != null) {
            List<Map<String, Object>> rows = query.getRows();
            if (!CollectionUtils.isEmpty(rows)) {
                for (Map<String, Object> row : rows) {
                    String deviceId = MapUtils.getString(row, "device_id");//主机柜id
                    row.put("device_kg_id", this.getProductByDeviceId(deviceId));//主机开关柜id
                    row.put("device_name", this.getDeviceNameByDeviceId(deviceId));
                }
                return rows;
            }
        }
        return Collections.emptyList();
    }

    @Override
    public String findDeviceKgIdByDeviceId(String deviceId) {
        validateDeviceId(deviceId);
        for (Map<String, Object> row : queryAllMainCabinetMetrics()) {
            if (StringUtils.equals(deviceId, MapUtils.getString(row, "device_id"))) {
                String deviceKgId = MapUtils.getString(row, "device_kg_id");
                if (StringUtils.isNotBlank(deviceKgId)) {
                    return deviceKgId;
                }
            }
        }
        return null;
    }

    @Override
    public String findDeviceIdByDeviceKgId(String deviceKgId) {
        if (StringUtils.isBlank(deviceKgId)) {
            throw new ServiceException("开关柜设备编号不能为空");
        }
        for (Map<String, Object> row : queryAllMainCabinetMetrics()) {
            if (StringUtils.equals(deviceKgId, MapUtils.getString(row, "device_kg_id"))) {
                String deviceId = MapUtils.getString(row, "device_id");
                if (StringUtils.isNotBlank(deviceId)) {
                    return deviceId;
                }
            }
        }
        return null;
    }

    /**
     * 根据主机返回对应主机开关柜
     *
     * @param deviceId
     * @return
     */
    private String getProductByDeviceId(String deviceId) {
        String area = RuoYiConfig.getArea();
        if (StringUtils.equals(area, "jht")) {
            switch (deviceId) {
                case Constants.MAIN_CABINET_1_ID_JHT:
                    return "1966040730050564097";
                case Constants.MAIN_CABINET_2_ID_JHT:
                    return "1968156453912731649";
                case Constants.MAIN_CABINET_3_ID_JHT:
                    return "1968156585165086722";
                case Constants.MAIN_CABINET_4_ID_JHT:
                    return "1968156675128713218";
                default:
                    return null; // 或者抛出异常，根据你的业务逻辑决定
            }
        } else if (StringUtils.equals(area, "sjn")) {
            switch (deviceId) {
                case Constants.MAIN_CABINET_1_ID_SJN:
                    return "1972227858623197185";
                case Constants.MAIN_CABINET_2_ID_SJN:
                    return "1972228146797047810";
                case Constants.MAIN_CABINET_3_ID_SJN:
                    return "1972228250530574337";
                case Constants.MAIN_CABINET_4_ID_SJN:
                    return "1972228407527567361";
                default:
                    return null; // 或者抱出异常，根据你的业务逻辑决定
            }
        }
        throw new RuntimeException("未找到正确地区");
    }

    private String getDeviceNameByDeviceId(String deviceId) {

        String area = RuoYiConfig.getArea();
        if (StringUtils.equals(area, "jht")) {
            switch (deviceId) {
                case Constants.MAIN_CABINET_1_ID_JHT:
                    return "1号主机";
                case Constants.MAIN_CABINET_2_ID_JHT:
                    return "2号主机";
                case Constants.MAIN_CABINET_3_ID_JHT:
                    return "3号主机";
                case Constants.MAIN_CABINET_4_ID_JHT:
                    return "4号主机";
                default:
                    return null; // 或者抛出异常，根据你的业务逻辑决定
            }
        } else if (StringUtils.equals(area, "sjn")) {
            switch (deviceId) {
                case Constants.MAIN_CABINET_1_ID_SJN:
                    return "1号主机";
                case Constants.MAIN_CABINET_2_ID_SJN:
                    return "2号主机";
                case Constants.MAIN_CABINET_3_ID_SJN:
                    return "3号主机";
                case Constants.MAIN_CABINET_4_ID_SJN:
                    return "4号主机";
                default:
                    return null; // 或者抛出异常，根据你的业务逻辑决定
            }
        }
        throw new RuntimeException("未找到正确地区");
    }


    @Override
    public BigDecimal queryTotalActivePower(String deviceKgId, DeviceTimeScope scope) {
        validateDeviceId(deviceKgId);
        TimeWindow window = buildTimeWindow(scope);
        return sumActivePower(deviceKgId, window, "queryTotalActivePower");
    }

    @Override
    public BigDecimal queryTotalReactivePower(String deviceKgId, DeviceTimeScope scope) {
        validateDeviceId(deviceKgId);
        TimeWindow window = buildTimeWindow(scope);
        String sql = String.format(
                "SELECT AVG(q) AS total_reactive_kw FROM %s WHERE device_id = '%s' AND time >= %s AND time < %s and q > 0",
                DATA_TABLE, deviceKgId, window.getStartLiteral(), window.getEndLiteral());
        log.info("queryTotalReactivePower: {}", sql);
        return extractDecimal(query(sql), "total_reactive_kw");
    }

    @Override
    public BigDecimal queryTotalActiveEnergy(String deviceKgId, DeviceTimeScope scope) {
        validateDeviceId(deviceKgId);
        TimeWindow window = buildTimeWindow(scope);
        BigDecimal sumActivePower = sumActivePower(deviceKgId, window, "queryTotalActiveEnergy");
        return sumActivePower.multiply(SAMPLE_INTERVAL_HOURS)
                .setScale(ENERGY_SCALE, RoundingMode.HALF_UP);
    }

    @Override
    public List<Map<String, Object>> queryRuntimeTrend(String deviceKgId, DeviceTimeScope scope) {
        validateDeviceId(deviceKgId);
        RuntimeTrendPlan plan = buildRuntimeTrendPlan(scope);
        TimeWindow window = plan.getQueryWindow();
        String sql = String.format(
                "SELECT time, isPowerOnPos FROM %s WHERE device_id = '%s' AND time >= %s AND time < %s "
                        + "AND isPowerOnPos IS NOT NULL ORDER BY time ASC",
                DATA_TABLE, deviceKgId, window.getStartLiteral(), window.getEndLiteral());
        log.info("queryRuntimeTrend: {}", sql);
        List<Map<String, Object>> rows = query(sql).getRows();
        Map<BucketPlan, BigDecimal> accumulator = plan.createAccumulator();
        accumulateRuntimeByBucket(plan, rows, accumulator);
        return plan.assemble(accumulator);
    }

    @Override
    public List<Map<String, Object>> queryActiveEnergySummary(String deviceKgId, DeviceTimeScope scope) {
        validateDeviceId(deviceKgId);
        EnergyScope energyScope = EnergyScope.fromDeviceScope(scope);
        ZoneId zone = ZoneId.systemDefault();
        ZonedDateTime now = ZonedDateTime.now(zone);
        Map<String, Object> entry = buildEnergySummary(deviceKgId, energyScope, zone, now);
        return Collections.singletonList(entry);
    }

    @Override
    public Map<String, Object> aggregateHostMetrics(DeviceTimeScope scope) {
        DeviceTimeScope actualScope = ensureCombinedScope(scope);
        List<Map<String, Object>> hosts = queryAllMainCabinetMetrics();
        if (CollectionUtils.isEmpty(hosts)) {
            return buildAggregateResult(actualScope, 0,
                    BigDecimal.ZERO, BigDecimal.ZERO, BigDecimal.ZERO, BigDecimal.ZERO);
        }
        BigDecimal runtime = BigDecimal.ZERO;
        BigDecimal activePower = BigDecimal.ZERO;
        BigDecimal reactivePower = BigDecimal.ZERO;
        BigDecimal totalEnergy = BigDecimal.ZERO;
        int count = 0;
        for (Map<String, Object> host : hosts) {
            String deviceKgId = MapUtils.getString(host, "device_kg_id");
            if (StringUtils.isBlank(deviceKgId)) {
                continue;
            }
            count++;
            runtime = runtime.add(safeDecimal(queryTotalRuntimeHours(deviceKgId, actualScope)));
            activePower = activePower.add(safeDecimal(queryTotalActivePower(deviceKgId, actualScope)));
            reactivePower = reactivePower.add(safeDecimal(queryTotalReactivePower(deviceKgId, actualScope)));
            totalEnergy = totalEnergy.add(extractEnergyTotal(deviceKgId, actualScope));
        }
        return buildAggregateResult(actualScope, count, runtime, activePower, reactivePower, totalEnergy);
    }

    @Override
    public List<Map<String, Object>> queryHostRunStatus(DeviceTimeScope scope) {
        DeviceTimeScope actualScope = ensureCombinedScope(scope);
        TimeWindow window = buildTimeWindow(actualScope);
        List<Map<String, Object>> hosts = queryAllMainCabinetMetrics();
        if (CollectionUtils.isEmpty(hosts)) {
            return Collections.emptyList();
        }
        List<Map<String, Object>> result = new ArrayList<>(hosts.size());
        for (Map<String, Object> host : hosts) {
            String deviceKgId = MapUtils.getString(host, "device_kg_id");
            if (StringUtils.isBlank(deviceKgId)) {
                continue;
            }
            String sql = String.format(
                    "SELECT MAX(CASE WHEN isPowerOnPos = 1 THEN 1 ELSE 0 END) AS run_flag "
                            + "FROM %s WHERE device_id = '%s' AND time >= %s AND time < %s",
                    DATA_TABLE, deviceKgId, window.getStartLiteral(), window.getEndLiteral());
            log.info("queryHostRunStatus: {}", sql);
            IotQueryResult queryResult = query(sql);
            List<Map<String, Object>> rows = queryResult == null ? Collections.emptyList() : queryResult.getRows();
            Integer flag = 0;
            if (!CollectionUtils.isEmpty(rows)) {
                Object value = rows.get(0).get("run_flag");
                flag = toBigDecimal(value) == null ? 0 : toBigDecimal(value).intValue();
                if (flag != null && flag > 0) {
                    flag = 1;
                } else {
                    flag = 0;
                }
            }
            Map<String, Object> item = new HashMap<>(4);
            item.put("device_name", MapUtils.getString(host, "device_name", "未命名主机"));
            item.put("device_kg_id", deviceKgId);
            item.put("isPowerOnPos", String.valueOf(flag));
            result.add(item);
        }
        return result;
    }

    @Override
    public List<Map<String, Object>> queryHostOperationReport(DeviceTimeScope scope, String deviceKgIdFilter) {
        DeviceTimeScope actualScope = ensureCombinedScope(scope);
        List<Map<String, Object>> hosts = queryAllMainCabinetMetrics();
        if (CollectionUtils.isEmpty(hosts)) {
            return Collections.emptyList();
        }
        Map<String, String> runStatusMap = buildRunStatusMap(actualScope);
        TimeWindow window = buildTimeWindow(actualScope);
        List<Map<String, Object>> result = new ArrayList<>();
        for (Map<String, Object> host : hosts) {
            String deviceKgId = MapUtils.getString(host, "device_kg_id");
            if (StringUtils.isBlank(deviceKgId)) {
                continue;
            }
            if (StringUtils.isNotBlank(deviceKgIdFilter) && !StringUtils.equals(deviceKgIdFilter, deviceKgId)) {
                continue;
            }
            Map<String, Object> row = new HashMap<>(12);
            String deviceName = MapUtils.getString(host, "device_name", "未命名主机");
            row.put("deviceName", deviceName);
            row.put("deviceKgId", deviceKgId);
            row.put("dateText", buildDateText(actualScope));
            row.put("isPowerOnPos", runStatusMap.getOrDefault(deviceKgId, "0"));
            BigDecimal totalActivePower = safeDecimal(queryTotalActivePower(deviceKgId, actualScope));
            BigDecimal totalReactivePower = safeDecimal(queryTotalReactivePower(deviceKgId, actualScope));
            BigDecimal runtimeHours = safeDecimal(queryTotalRuntimeHours(deviceKgId, actualScope));
            BigDecimal energy = safeDecimal(extractEnergyTotal(deviceKgId, actualScope));
            BigDecimal avgPowerFactor = queryAveragePowerFactor(deviceKgId, window);
            row.put("totalActivePowerKw", totalActivePower.setScale(2, RoundingMode.HALF_UP));
            row.put("totalReactivePowerKw", totalReactivePower.setScale(2, RoundingMode.HALF_UP));
            row.put("runtimeHours", runtimeHours.setScale(2, RoundingMode.HALF_UP));
            row.put("totalEnergyKwh", energy.setScale(2, RoundingMode.HALF_UP));
            row.put("powerFactor", avgPowerFactor.setScale(2, RoundingMode.HALF_UP));
            result.add(row);
        }
        return result;
    }

    private BigDecimal sumActivePower(String deviceId, TimeWindow window, String logPrefix) {
        String sql = String.format(
                "SELECT AVG(p) AS total_active_kw FROM %s WHERE device_id = '%s' AND time >= %s AND time < %s and p > 0",
                DATA_TABLE, deviceId, window.getStartLiteral(), window.getEndLiteral());
        log.info("{}: {}", logPrefix, sql);
        return extractDecimal(query(sql), "total_active_kw");
    }

    private List<Map<String, Object>> fetchPowerRuntimeRows(String deviceId, TimeWindow window, String logPrefix) {
        String sql = String.format(
                "SELECT time, isPowerOnPos, p FROM %s WHERE device_id = '%s' AND time >= %s AND time < %s "
                        + "AND isPowerOnPos IS NOT NULL ORDER BY time ASC",
                DATA_TABLE, deviceId, window.getStartLiteral(), window.getEndLiteral());
        log.info("{}: {}", logPrefix, sql);
        return query(sql).getRows();
    }

    private Map<String, Object> buildEnergySummary(String deviceKgId,
                                                   EnergyScope scope,
                                                   ZoneId zone,
                                                   ZonedDateTime now) {
        ZonedDateTime start = scope.rangeStart(zone, now);
        TimeWindow window = buildTimeWindow(start, now);
        List<Map<String, Object>> rows = fetchPowerRuntimeRows(
                deviceKgId, window, "queryActiveEnergySummary[" + scope.getCode() + "]");
        long runtimeMillis = accumulateRuntimeMillis(window, rows);
        BigDecimal runtimeHours = toRuntimeHours(runtimeMillis);
        BigDecimal averagePower = calculateAverageActivePower(rows);
        BigDecimal energy = runtimeHours.multiply(averagePower)
                .setScale(ENERGY_SCALE, RoundingMode.HALF_UP);
        Map<String, Object> entry = new LinkedHashMap<>(4);
        entry.put("scope", scope.getCode());
        entry.put("scopeName", scope.getDescription());
        entry.put("totalEnergyKwh", energy.setScale(2, RoundingMode.HALF_UP));
        return entry;
    }

    private Map<String, Object> buildAggregateResult(DeviceTimeScope scope,
                                                     int count,
                                                     BigDecimal runtime,
                                                     BigDecimal activePower,
                                                     BigDecimal reactivePower,
                                                     BigDecimal energy) {
        Map<String, Object> data = new LinkedHashMap<>(8);
        data.put("scope", scope.getCode());
        data.put("scopeName", scope.getDescription());
        data.put("hostCount", count);
        data.put("runtimeHours", runtime.setScale(2, RoundingMode.HALF_UP));
        data.put("totalActivePowerKw", activePower.setScale(2, RoundingMode.HALF_UP));
        data.put("totalReactivePowerKw", reactivePower.setScale(2, RoundingMode.HALF_UP));
        data.put("totalEnergyKwh", energy.setScale(2, RoundingMode.HALF_UP));
        return data;
    }

    private BigDecimal extractEnergyTotal(String deviceKgId, DeviceTimeScope scope) {
        try {
            List<Map<String, Object>> summary = queryActiveEnergySummary(deviceKgId, scope);
            if (CollectionUtils.isEmpty(summary)) {
                return BigDecimal.ZERO;
            }
            Object value = summary.get(0).get("totalEnergyKwh");
            return toBigDecimal(value) == null ? BigDecimal.ZERO : toBigDecimal(value);
        } catch (ServiceException ex) {
            log.warn("聚合能耗时出错, deviceKgId={}, scope={}", deviceKgId, scope, ex);
            return BigDecimal.ZERO;
        }
    }

    private BigDecimal safeDecimal(BigDecimal value) {
        if (value == null) {
            return BigDecimal.ZERO;
        }
        return value;
    }

    private Map<String, String> buildRunStatusMap(DeviceTimeScope scope) {
        List<Map<String, Object>> statuses = queryHostRunStatus(scope);
        Map<String, String> map = new HashMap<>(statuses.size());
        for (Map<String, Object> status : statuses) {
            String deviceKgId = MapUtils.getString(status, "device_kg_id");
            String flag = MapUtils.getString(status, "isPowerOnPos", "0");
            if (StringUtils.isNotBlank(deviceKgId)) {
                map.put(deviceKgId, flag);
            }
        }
        return map;
    }

    private BigDecimal queryAveragePowerFactor(String deviceKgId, TimeWindow window) {
        String sql = String.format(
                "SELECT AVG(powerfactor) AS avg_pf FROM %s WHERE device_id = '%s' AND time >= %s AND time < %s "
                        + "AND powerfactor IS NOT NULL",
                DATA_TABLE, deviceKgId, window.getStartLiteral(), window.getEndLiteral());
        log.info("queryAveragePowerFactor: {}", sql);
        IotQueryResult result = query(sql);
        if (result == null || CollectionUtils.isEmpty(result.getRows())) {
            return BigDecimal.ZERO;
        }
        Object value = result.getRows().get(0).get("avg_pf");
        BigDecimal decimal = toBigDecimal(value);
        return decimal == null ? BigDecimal.ZERO : decimal;
    }

    private String buildDateText(DeviceTimeScope scope) {
        ZonedDateTime now = ZonedDateTime.now();
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy年M月d日");
        if (scope == DeviceTimeScope.TODAY) {
            return now.format(formatter);
        }
        if (scope == DeviceTimeScope.MONTH) {
            return now.format(DateTimeFormatter.ofPattern("yyyy年M月"));
        }
        return now.format(DateTimeFormatter.ofPattern("yyyy年"));
    }

    private BigDecimal calculateAverageActivePower(List<Map<String, Object>> rows) {
        if (CollectionUtils.isEmpty(rows)) {
            return ZERO_HOURS;
        }
        BigDecimal sum = BigDecimal.ZERO;
        int count = 0;
        for (Map<String, Object> row : rows) {
            Boolean powerOn = parsePowerOnFlag(row.get("isPowerOnPos"));
            if (!Boolean.TRUE.equals(powerOn)) {
                continue;
            }
            BigDecimal power = toBigDecimal(row.get("p"));
            if (power == null) {
                continue;
            }
            sum = sum.add(power);
            count++;
        }
        if (count <= 0) {
            return ZERO_HOURS;
        }
        return sum.divide(BigDecimal.valueOf(count), ENERGY_SCALE, RoundingMode.HALF_UP);
    }

    private BigDecimal toRuntimeHours(long runtimeMillis) {
        if (runtimeMillis <= 0L) {
            return ZERO_HOURS;
        }
        return BigDecimal.valueOf(runtimeMillis)
                .divide(BigDecimal.valueOf(MILLIS_PER_HOUR), ENERGY_SCALE, RoundingMode.HALF_UP);
    }

    private RuntimeTrendPlan buildRuntimeTrendPlan(DeviceTimeScope scope) {
        ZoneId zone = ZoneId.systemDefault();
        ZonedDateTime now = ZonedDateTime.now(zone);
        switch (scope) {
            case TODAY:
                ZonedDateTime dayStart = scope.rangeStart();
                ZonedDateTime hourlyEnd = now.truncatedTo(ChronoUnit.HOURS).plusHours(1);
                if (!hourlyEnd.isAfter(dayStart)) {
                    hourlyEnd = dayStart.plusHours(1);
                }
                TimeWindow todayWindow = buildTimeWindow(dayStart, hourlyEnd);
                List<BucketPlan> hourlyBuckets = new ArrayList<>();
                for (ZonedDateTime cursor = todayWindow.getStart();
                     cursor.isBefore(todayWindow.getEnd());
                     cursor = cursor.plusHours(1)) {
                    ZonedDateTime bucketEnd = cursor.plusHours(1);
                    String label = String.format("%02d:00", cursor.getHour());
                    hourlyBuckets.add(new BucketPlan(cursor, bucketEnd, label));
                }
                return new RuntimeTrendPlan(todayWindow, "1h", hourlyBuckets);
            case QUARTER:
                ZonedDateTime quarterStart = scope.rangeStart();
                ZonedDateTime quarterEnd = quarterStart.plusMonths(3);
                if (now.isBefore(quarterEnd)) {
                    quarterEnd = now;
                }
                if (!quarterEnd.isAfter(quarterStart)) {
                    quarterEnd = quarterStart.plusDays(1);
                }
                TimeWindow quarterWindow = buildTimeWindow(quarterStart, quarterEnd);
                List<BucketPlan> quarterBuckets = new ArrayList<>(3);
                for (ZonedDateTime cursor = quarterWindow.getStart();
                     cursor.isBefore(quarterWindow.getEnd());
                     cursor = cursor.plusMonths(1)) {
                    ZonedDateTime bucketEnd = cursor.plusMonths(1);
                    String label = cursor.getMonthValue() + "\u6708";
                    quarterBuckets.add(new BucketPlan(cursor, bucketEnd, label));
                }
                return new RuntimeTrendPlan(quarterWindow, "1d", quarterBuckets);
            case YEAR:
                ZonedDateTime yearStart = scope.rangeStart();
                ZonedDateTime yearEnd = yearStart.plusYears(1);
                if (now.isBefore(yearEnd)) {
                    yearEnd = now;
                }
                if (!yearEnd.isAfter(yearStart)) {
                    yearEnd = yearStart.plusDays(1);
                }
                TimeWindow yearWindow = buildTimeWindow(yearStart, yearEnd);
                List<BucketPlan> monthlyBuckets = new ArrayList<>(12);
                for (ZonedDateTime cursor = yearWindow.getStart();
                     cursor.isBefore(yearWindow.getEnd());
                     cursor = cursor.plusMonths(1)) {
                    ZonedDateTime bucketEnd = cursor.plusMonths(1);
                    String label = cursor.getMonthValue() + "\u6708";
                    monthlyBuckets.add(new BucketPlan(cursor, bucketEnd, label));
                }
                return new RuntimeTrendPlan(yearWindow, "1d", monthlyBuckets);
            case MONTH:
            default:
                ZonedDateTime monthStart = scope.rangeStart();
                ZonedDateTime monthEnd = monthStart.plusMonths(1);
                if (now.isBefore(monthEnd)) {
                    monthEnd = now;
                }
                if (!monthEnd.isAfter(monthStart)) {
                    monthEnd = monthStart.plusDays(1);
                }
                TimeWindow monthWindow = buildTimeWindow(monthStart, monthEnd);
                List<BucketPlan> dailyBuckets = new ArrayList<>();
                for (ZonedDateTime cursor = monthWindow.getStart();
                     cursor.isBefore(monthWindow.getEnd());
                     cursor = cursor.plusDays(1)) {
                    ZonedDateTime bucketEnd = cursor.plusDays(1);
                    String label = cursor.getDayOfMonth() + "\u65e5";
                    dailyBuckets.add(new BucketPlan(cursor, bucketEnd, label));
                }
                return new RuntimeTrendPlan(monthWindow, "1d", dailyBuckets);
        }
    }

    private HealthScoreTrendPlan buildHealthScoreTrendPlan(DeviceTimeScope scope) {
        ZoneId zone = ZoneId.systemDefault();
        ZonedDateTime start = scope.rangeStart();
        if (!start.getZone().equals(zone)) {
            start = start.withZoneSameInstant(zone);
        }
        switch (scope) {
            case TODAY:
                ZonedDateTime now = ZonedDateTime.now(zone);
                ZonedDateTime hourlyEnd = now.truncatedTo(ChronoUnit.HOURS).plusHours(1);
                if (!hourlyEnd.isAfter(start)) {
                    hourlyEnd = start.plusHours(1);
                }
                TimeWindow todayWindow = buildTimeWindow(start, hourlyEnd);
                List<BucketPlan> hourlyBuckets = new ArrayList<>();
                for (ZonedDateTime cursor = todayWindow.getStart();
                     cursor.isBefore(todayWindow.getEnd());
                     cursor = cursor.plusHours(1)) {
                    ZonedDateTime bucketEnd = cursor.plusHours(1);
                    String label = String.format("%02d:00", cursor.getHour());
                    hourlyBuckets.add(new BucketPlan(cursor, bucketEnd, label));
                }
                return new HealthScoreTrendPlan(todayWindow, hourlyBuckets);
            case YEAR:
                ZonedDateTime yearEnd = start.plusYears(1);
                TimeWindow yearWindow = buildTimeWindow(start, yearEnd);
                List<BucketPlan> monthlyBuckets = new ArrayList<>(12);
                for (ZonedDateTime cursor = yearWindow.getStart();
                     cursor.isBefore(yearWindow.getEnd());
                     cursor = cursor.plusMonths(1)) {
                    ZonedDateTime bucketEnd = cursor.plusMonths(1);
                    String label = cursor.getMonthValue() + "\u6708";
                    monthlyBuckets.add(new BucketPlan(cursor, bucketEnd, label));
                }
                return new HealthScoreTrendPlan(yearWindow, monthlyBuckets);
            case QUARTER:
                ZonedDateTime quarterEnd = start.plusMonths(3);
                TimeWindow quarterWindow = buildTimeWindow(start, quarterEnd);
                List<BucketPlan> quarterBuckets = new ArrayList<>(3);
                for (ZonedDateTime cursor = quarterWindow.getStart();
                     cursor.isBefore(quarterWindow.getEnd());
                     cursor = cursor.plusMonths(1)) {
                    ZonedDateTime bucketEnd = cursor.plusMonths(1);
                    String label = cursor.getMonthValue() + "\u6708";
                    quarterBuckets.add(new BucketPlan(cursor, bucketEnd, label));
                }
                return new HealthScoreTrendPlan(quarterWindow, quarterBuckets);
            case MONTH:
            default:
                ZonedDateTime monthEnd = start.plusMonths(1);
                TimeWindow monthWindow = buildTimeWindow(start, monthEnd);
                List<BucketPlan> dailyBuckets = new ArrayList<>();
                for (ZonedDateTime cursor = monthWindow.getStart();
                     cursor.isBefore(monthWindow.getEnd());
                     cursor = cursor.plusDays(1)) {
                    ZonedDateTime bucketEnd = cursor.plusDays(1);
                    String label = cursor.getDayOfMonth() + "\u65e5";
                    dailyBuckets.add(new BucketPlan(cursor, bucketEnd, label));
                }
                return new HealthScoreTrendPlan(monthWindow, dailyBuckets);
        }
    }

    @Override
    public List<Map<String, Object>> queryLatestRecord(String deviceId) {
        validateDeviceId(deviceId);
        String sql = String.format(
                "SELECT * FROM %s WHERE device_id = '%s' ORDER BY time DESC LIMIT 1",
                HOST_DEVICE, deviceId);
        log.info("queryLatestRecord: {}", sql);
        List<Map<String, Object>> rows = query(sql).getRows();
        if (rows.isEmpty()) {
            return Collections.emptyList();
        }
        Map<String, Object> latestRow = rows.get(0);
        List<Map<String, Object>> attributes = new ArrayList<>(latestRow.size());
        for (Map.Entry<String, Object> entry : latestRow.entrySet()) {
            String column = entry.getKey();
            Object value = entry.getValue();
            Map<String, Object> attribute = new LinkedHashMap<>(3);
            attribute.put("field", column);
            attribute.put("value", value);
            attribute.put("description", resolveDescription(column));
            attributes.add(attribute);
        }
        return attributes;
    }

    @Override
    public List<Map<String, Object>> queryFaultRecords(String deviceKgId, DeviceTimeScope scope) {
        validateDeviceId(deviceKgId);
        ZoneId zone = ZoneId.systemDefault();
        LocalDateTime start = resolveScopeStart(scope, zone);
        LocalDateTime end = ZonedDateTime.now(zone).toLocalDateTime();
        // jht deviceKgId
        // sjn deviceId
        String area = RuoYiConfig.getArea();
        boolean isJht = StringUtils.equals(area, "jht");

        if(! isJht){
            deviceKgId = findDeviceIdByDeviceKgId(deviceKgId);
        }

        List<DeviceFaultWindow> windows = faultWindowMapper.selectByRange(deviceKgId, start, end);
        if (CollectionUtils.isEmpty(windows)) {
            return Collections.emptyList();
        }
        List<Map<String, Object>> records = new ArrayList<>(windows.size());
        for (DeviceFaultWindow window : windows) {
            records.add(buildFaultRecord(window));
        }
        return records;
    }

    /**
     * 查询温度字段在给定时间范围内的最高/最低/平均值，并输出合并文案。
     *
     * @param deviceId 主机设备编号
     * @param scope    查询范围（仅支持本日、本月、本年度）
     * @return 温度统计列表
     */
    @Override
    public List<Map<String, Object>> queryDeviceTemperatureStats(String deviceId, DeviceTimeScope scope,
                                                                 LocalDateTime startTime, LocalDateTime endTime) {
        validateDeviceId(deviceId);
        DeviceTimeScope baseScope = ensureCombinedScope(scope);
        DeviceTimeScope effectiveScope = adaptScope(baseScope, startTime, endTime);
        TimeWindow window = buildTimeWindow(effectiveScope, startTime, endTime);
        String sql = String.format(
                "SELECT %s FROM %s WHERE device_id = '%s' AND time >= %s AND time < %s",
                TEMPERATURE_STATS_SELECT, HOST_DEVICE, deviceId,
                window.getStartLiteral(), window.getEndLiteral());
        log.info("queryDeviceTemperatureStats: {}", sql);
        IotQueryResult result = query(sql);
        if (result == null || CollectionUtils.isEmpty(result.getRows())) {
            return Collections.emptyList();
        }
        Map<String, Object> aggregatedRow = result.getRows().get(0);
        return buildTemperatureStatRows(aggregatedRow);
    }

    /**
     * 查询温度字段的趋势数据，今日按小时、本月按天、本年度按月聚合为平均值。
     *
     * @param deviceId 主机设备编号
     * @param scope    时间范围（仅支持本日、本月、本年度）
     * @return 每个时间桶的温度平均值集合
     */
    @Override
    public List<Map<String, Object>> queryDeviceTemperatureTrend(String deviceId, DeviceTimeScope scope,
                                                                 LocalDateTime startTime, LocalDateTime endTime) {
        validateDeviceId(deviceId);
        DeviceTimeScope baseScope = ensureCombinedScope(scope);
        DeviceTimeScope effectiveScope = adaptScope(baseScope, startTime, endTime);
        TimeWindow window = buildTimeWindow(effectiveScope, startTime, endTime);
        TemperatureTrendPlan plan = buildTemperatureTrendPlan(effectiveScope, window);
        if (plan.isEmpty()) {
            return Collections.emptyList();
        }
        String sql = String.format(
                "SELECT time, %s FROM %s WHERE device_id = '%s' AND time >= %s AND time < %s ORDER BY time ASC",
                String.join(", ", TEMPERATURE_FIELDS),
                HOST_DEVICE, deviceId, window.getStartLiteral(), window.getEndLiteral());
        log.info("queryDeviceTemperatureTrend: {}", sql);
        IotQueryResult result = query(sql);
        Map<BucketPlan, TemperatureAccumulator> accumulator = plan.createAccumulator();
        accumulateTemperatureByBucket(accumulator, result == null ? null : result.getRows());
        return plan.assemble(accumulator);
    }

    /**
     * 分页查询开关柜与主机数据，并按时间对齐合并温度、电参字段。
     *
     * @param deviceKgId 开关柜设备编号
     * @param scope      查询范围（仅支持本日、本月、本年度）
     * @param pageNum    页码，默认为1
     * @param pageSize   每页数量，默认为10
     * @return 包含分页信息及组合记录的结构化结果
     */
    @Override
    public Map<String, Object> queryCombinedDeviceSnapshots(String deviceKgId, DeviceTimeScope scope,
                                                            Integer pageNum, Integer pageSize,
                                                            LocalDateTime startTime, LocalDateTime endTime) {
        validateDeviceId(deviceKgId);
        DeviceTimeScope baseScope = ensureCombinedScope(scope);
        DeviceTimeScope effectiveScope = adaptScope(baseScope, startTime, endTime);
        String deviceId = findDeviceIdByDeviceKgId(deviceKgId);
        if (StringUtils.isBlank(deviceId)) {
            throw new ServiceException("未找到开关柜对应的主机设备编号");
        }
        int size = (pageSize == null || pageSize <= 0) ? 10 : pageSize;
        int page = (pageNum == null || pageNum <= 0) ? 1 : pageNum;
        TimeWindow window = buildTimeWindow(effectiveScope, startTime, endTime);
        long total = countSwitchDeviceRows(deviceKgId, window);
        List<Map<String, Object>> records = Collections.emptyList();
        if (total > 0L && size > 0) {
            List<Map<String, Object>> switchRows = fetchSwitchDevicePage(deviceKgId, window, page, size);
            if (!CollectionUtils.isEmpty(switchRows)) {
                List<Long> timestamps = extractTimestamps(switchRows);
                Map<Long, Map<String, Object>> hostIndex = fetchHostRowsByTimestamps(deviceId, timestamps);
                records = new ArrayList<>(switchRows.size());
                for (Map<String, Object> switchRow : switchRows) {
                    long timestamp = parseEpochMillis(switchRow.get("time"));
                    Map<String, Object> hostRow = hostIndex.get(timestamp);
                    records.add(buildCombinedRecord(switchRow, hostRow, deviceId));
                }
            }
        }
        return buildCombinedPageResponse(deviceId, deviceKgId, effectiveScope, page, size, total, records);
    }

    @Override
    public BigDecimal queryTotalRuntimeHours(String deviceKgId, DeviceTimeScope scope) {
        validateDeviceId(deviceKgId);
        TimeWindow window = buildTimeWindow(scope);
        String sql = String.format(
                "SELECT time, isPowerOnPos FROM %s WHERE device_id = '%s' AND time >= %s AND time < %s "
                        + "AND isPowerOnPos IS NOT NULL ORDER BY time ASC",
                DATA_TABLE, deviceKgId, window.getStartLiteral(), window.getEndLiteral());
        log.info("queryTotalRuntimeHours: {}", sql);
        List<Map<String, Object>> rows = query(sql).getRows();
        long runtimeMillis = accumulateRuntimeMillis(window, rows);
        return toRuntimeHours(runtimeMillis);
    }

    /**
     * 查询健康评分历史趋势。
     * <p>
     * - 校验入参：validateDeviceId(deviceId)（同文件:460）确保设备编号非空，避免无效查询。
     * - 构建时间窗口：buildHealthScoreTrendPlan(scope)（:361）依据DeviceTimeScope拆分查询区间与桶（Bucket）。
     * - 若scope=TODAY：start = 今日0点，end = min(当前整点+1h, start+24h)，桶宽为1小时。
     * - 若scope=MONTH：start = 当月1日0点，end = start + 1月，桶宽为1天。
     * - 若scope=QUARTER：start = 当季首月1日0点，end = start + 3月，桶宽为1月。
     * - 若scope=YEAR：start = 当年1月1日0点，end = start + 1年，桶宽为1月。
     * - 生成的BucketPlan记录[bucket_start, bucket_end)与显示标签label。
     * - 拉取原始评分：healthScoreMapper.selectByRange(deviceId, window.start, window.end)（映射SQL见ruoyi-iot/src/main/resources/mapper/iot/DeviceHealthScoreMapper.xml:36），从表iot_device_health_score读取满足score_time
     * ∈ [start, end)的记录，字段score_total为单次评分。
     * - 分桶累积：遍历每条DeviceHealthScore记录（:481），将score_time换算为毫秒并匹配对应桶bucket（BucketPlan.contains）。累积器ScoreAccumulator（:765）执行：
     * - sum_bucket = sum_bucket + score_total
     * - count_bucket = count_bucket + 1
     * - 计算平均值：对每个桶调用ScoreAccumulator.average()（:776），若count_bucket>0则 score_avg = round(sum_bucket / count_bucket, 2)（四舍五入到2位小数），否则 score_avg = 0。
     * - 组装结果：plan.assemble(...)（:751）输出列表元素，每项包含：
     * - label（如 “05:00”、“12日”、“3月”等）
     * - startTime、endTime（桶区间的字符串表示）
     * - scoreTotal = score_avg（对应桶的平均健康分）
     */
    @Override
    public List<Map<String, Object>> queryHealthScoreHistory(String deviceId, DeviceTimeScope scope) {
        validateDeviceId(deviceId);
        HealthScoreTrendPlan plan = buildHealthScoreTrendPlan(scope);
        TimeWindow window = plan.getQueryWindow();
        ZoneId zone = ZoneId.systemDefault();
        List<DeviceHealthScore> scores = healthScoreMapper.selectByRange(
                deviceId,
                window.getStart().toLocalDateTime(),
                window.getEnd().toLocalDateTime());
        Map<BucketPlan, ScoreAccumulator> accumulator = plan.createAccumulator();
        if (!CollectionUtils.isEmpty(scores)) {
            for (DeviceHealthScore score : scores) {
                LocalDateTime scoreTime = score.getScoreTime();
                BigDecimal total = score.getScoreTotal();
                if (scoreTime == null || total == null) {
                    continue;
                }
                long epochMillis = scoreTime.atZone(zone).toInstant().toEpochMilli();
                plan.accumulate(accumulator, epochMillis, total);
            }
        }
        return plan.assemble(accumulator);
    }

    /**
     * 查询最新健康评分。
     */
    @Override
    public Map<String, Object> queryLatestHealthScore(String deviceId) {
        validateDeviceId(deviceId);
        DeviceHealthScore score = healthScoreMapper.selectLatestOne(deviceId);
        if (score == null) {
            return Collections.emptyMap();
        }
        Map<String, Object> result = new LinkedHashMap<>(10);
        result.put("deviceId", score.getDeviceId());
        result.put("scoreTime", score.getScoreTime());
        result.put("scoreTotal", score.getScoreTotal());
        result.put("scoreAlarm", score.getScoreAlarm());
        result.put("scoreFault", score.getScoreFault());
        result.put("scoreTemperature",score.getScoreTemperature());
        result.put("scoreElectrical",score.getScoreElectrical());
        return result;
    }

    private Map<String, Object> buildFaultRecord(DeviceFaultWindow window) {
        Map<String, Object> record = new LinkedHashMap<>(5);
        record.put("faultDescription", buildFaultDescription(window));
        record.put("reportTime", window.getWindowEnd());
        record.put("reportMethod", AUTO_REPORT_METHOD);
        /*record.put("faultTotalCnt", window.getTotalFaultCount());
        record.put("alarmTotalCnt", window.getAlarmTotalCount());*/
        return record;
    }

    private String buildFaultDescription(DeviceFaultWindow window) {
        List<String> parts = new ArrayList<>();
        for (Map.Entry<String, String> entry : FAULT_COUNT_DESCRIPTION_MAP.entrySet()) {
            Integer count = readFaultCount(window, entry.getKey());
            if (count != null && count > 0) {
                parts.add(String.format(Locale.CHINA, "%s：%d", entry.getValue(), count));
            }
        }
        if (parts.isEmpty()) {
            return "当前窗口未检测到异常";
        }
        return String.join("；", parts);
    }

    private Integer readFaultCount(DeviceFaultWindow window, String fieldName) {
        Field field = FAULT_COUNT_FIELD_MAP.get(fieldName);
        if (field == null) {
            return null;
        }
        try {
            Object value = field.get(window);
            return value instanceof Integer ? (Integer) value : null;
        } catch (IllegalAccessException ex) {
            log.debug("读取故障字段失败, field={}", fieldName, ex);
            return null;
        }
    }

    /**
     * 校验并归一化分页查询的时间范围，仅允许本日/本月/本年度。
     */
    private DeviceTimeScope ensureCombinedScope(DeviceTimeScope scope) {
        DeviceTimeScope actual = scope == null ? DeviceTimeScope.MONTH : scope;
        switch (actual) {
            case TODAY:
            case MONTH:
            case YEAR:
                return actual;
            default:
                throw new ServiceException("时间范围仅支持本日、本月、本年度");
        }
    }

    /**
     * 根据自定义时间范围自适应粒度：<=24小时按本日，<=31天按本月，其余按本年度。
     */
    private DeviceTimeScope adaptScope(DeviceTimeScope scope, LocalDateTime start, LocalDateTime end) {
        if (start == null || end == null) {
            return scope;
        }
        long hours = Math.abs(Duration.between(start, end).toHours());
        if (hours <= 24) {
            return DeviceTimeScope.TODAY;
        }
        long days = Math.max(1L, hours / 24);
        if (days <= 31) {
            return DeviceTimeScope.MONTH;
        }
        return DeviceTimeScope.YEAR;
    }

    /**
     * 统计开关柜电参数据在窗口内的记录条数。
     */
    private long countSwitchDeviceRows(String deviceKgId, TimeWindow window) {
        String sql = String.format(
                "SELECT COUNT(1) AS total_count FROM %s WHERE device_id = '%s' AND time >= %s AND time < %s",
                DATA_TABLE, deviceKgId, window.getStartLiteral(), window.getEndLiteral());
        log.info("countSwitchDeviceRows: {}", sql);
        IotQueryResult result = query(sql);
        if (result == null || CollectionUtils.isEmpty(result.getRows())) {
            return 0L;
        }
        Object value = result.getRows().get(0).get("total_count");
        return toLong(value);
    }

    /**
     * 按时间倒序分页拉取开关柜电参字段。
     */
    private List<Map<String, Object>> fetchSwitchDevicePage(String deviceKgId, TimeWindow window,
                                                           int pageNum, int pageSize) {
        int safePageNum = Math.max(pageNum, 1);
        int safePageSize = Math.max(pageSize, 1);
        int offset = (safePageNum - 1) * safePageSize;
        String sql = String.format(
                "SELECT device_id, time, uab, ubc, uca, ia, ib, ic, p, q, f, powerfactor "
                        + "FROM %s WHERE device_id = '%s' AND time >= %s AND time < %s "
                        + "ORDER BY time DESC LIMIT %d OFFSET %d",
                DATA_TABLE, deviceKgId, window.getStartLiteral(), window.getEndLiteral(), safePageSize, offset);
        log.info("fetchSwitchDevicePage: {}", sql);
        IotQueryResult result = query(sql);
        if (result == null || CollectionUtils.isEmpty(result.getRows())) {
            return Collections.emptyList();
        }
        return result.getRows();
    }

    /**
     * 提取记录列表中的毫秒时间戳。
     */
    private List<Long> extractTimestamps(List<Map<String, Object>> rows) {
        if (CollectionUtils.isEmpty(rows)) {
            return Collections.emptyList();
        }
        List<Long> timestamps = new ArrayList<>(rows.size());
        for (Map<String, Object> row : rows) {
            long epochMillis = parseEpochMillis(row.get("time"));
            if (epochMillis > 0L) {
                timestamps.add(epochMillis);
            }
        }
        return timestamps;
    }

    /**
     * 批量查询主机设备在指定时间点附近的温度数据，并为每个时间戳挑选最近的一条记录。
     */
    private Map<Long, Map<String, Object>> fetchHostRowsByTimestamps(String deviceId,
                                                                     List<Long> timestamps) {
        if (CollectionUtils.isEmpty(timestamps)) {
            return Collections.emptyMap();
        }
        Set<Long> unique = new LinkedHashSet<>(timestamps);
        List<Long> sortedTargets = unique.stream().sorted().collect(Collectors.toList());
        long min = sortedTargets.get(0);
        long max = sortedTargets.get(sortedTargets.size() - 1);
        long marginMillis = 600_000L; // 10分钟缓冲区
        long startQuery = Math.max(0L, min - marginMillis);
        long endQuery = max + marginMillis;
        ZoneId zone = ZoneId.systemDefault();
        String sql = String.format(
                "SELECT device_id, time, astatortemp1, bstatortemp1, cstatortemp1, "
                        + "astatortemp2, bstatortemp2, cstatortemp2, nonshaftendtemp, shaftendtemp "
                        + "FROM %s WHERE device_id = '%s' AND time >= %s AND time <= %s ORDER BY time ASC",
                HOST_DEVICE, deviceId,
                formatTimestampLiteral(Instant.ofEpochMilli(startQuery).atZone(zone)),
                formatTimestampLiteral(Instant.ofEpochMilli(endQuery).atZone(zone)));
        log.info("fetchHostRowsByTimestamps: {}", sql);
        IotQueryResult result = query(sql);
        List<Map<String, Object>> rows = result == null ? Collections.emptyList() : result.getRows();
        if (CollectionUtils.isEmpty(rows)) {
            return Collections.emptyMap();
        }
        List<RowPoint> rowPoints = new ArrayList<>(rows.size());
        for (Map<String, Object> row : rows) {
            long epochMillis = parseEpochMillis(row.get("time"));
            if (epochMillis > 0L) {
                rowPoints.add(new RowPoint(epochMillis, row));
            }
        }
        if (rowPoints.isEmpty()) {
            return Collections.emptyMap();
        }
        Map<Long, Map<String, Object>> index = new HashMap<>(sortedTargets.size() * 2);
        int pointer = 0;
        for (long target : sortedTargets) {
            while (pointer < rowPoints.size() && rowPoints.get(pointer).epochMillis < target) {
                pointer++;
            }
            Map<String, Object> candidate = null;
            if (pointer >= rowPoints.size()) {
                candidate = rowPoints.get(rowPoints.size() - 1).row;
            } else if (pointer == 0) {
                candidate = rowPoints.get(0).row;
            } else {
                RowPoint after = rowPoints.get(pointer);
                RowPoint before = rowPoints.get(pointer - 1);
                long diffAfter = Math.abs(after.epochMillis - target);
                long diffBefore = Math.abs(target - before.epochMillis);
                candidate = diffBefore <= diffAfter ? before.row : after.row;
            }
            if (candidate != null) {
                index.put(target, candidate);
            }
        }
        return index;
    }

    /**
     * 构建温度统计结果列表。
     */
    private List<Map<String, Object>> buildTemperatureStatRows(Map<String, Object> aggregatedRow) {
        if (aggregatedRow == null || aggregatedRow.isEmpty()) {
            return Collections.emptyList();
        }
        List<Map<String, Object>> rows = new ArrayList<>(TEMPERATURE_FIELDS.length * 3);
        for (String field : TEMPERATURE_FIELDS) {
            String description = resolveDescription(field);
            appendTemperatureStatRow(rows, "max", STAT_LABEL_MAX, field, description,
                    aggregatedRow.get(field + "_max"));
            appendTemperatureStatRow(rows, "min", STAT_LABEL_MIN, field, description,
                    aggregatedRow.get(field + "_min"));
            appendTemperatureStatRow(rows, "avg", STAT_LABEL_AVG, field, description,
                    aggregatedRow.get(field + "_avg"));
        }
        return rows;
    }

    /**
     * 附加单条温度统计记录。
     */
    /**
     * 附加单条温度统计记录。
     */
    private void appendTemperatureStatRow(List<Map<String, Object>> target,
                                          String statType,
                                          String statLabel,
                                          String field,
                                          String fieldLabel,
                                          Object sourceValue) {
        BigDecimal value = toBigDecimal(sourceValue);
        Map<String, Object> row = new LinkedHashMap<>(6);
        row.put("statType", statType);
        row.put("statLabel", statLabel);
        row.put("field", field);
        row.put("fieldLabel", fieldLabel);
        row.put("value", value);
        row.put("text", formatTemperatureValue(value) + "°C");
        target.add(row);
    }

    /**
     * 构建温度趋势的时间桶计划。
     */
    private TemperatureTrendPlan buildTemperatureTrendPlan(DeviceTimeScope scope, TimeWindow window) {
        ZonedDateTime cursor = window.getStart();
        ZonedDateTime end = window.getEnd();
        List<BucketPlan> buckets = new ArrayList<>();
        while (cursor.isBefore(end)) {
            ZonedDateTime bucketEnd;
            String label;
            switch (scope) {
                case TODAY:
                    bucketEnd = cursor.plusHours(1);
                    label = cursor.getHour() + "时";
                    break;
                case YEAR:
                    bucketEnd = cursor.plusMonths(1);
                    label = cursor.getMonthValue() + "月";
                    break;
                case MONTH:
                default:
                    bucketEnd = cursor.plusDays(1);
                    label = cursor.getDayOfMonth() + "日";
                    break;
            }
            if (bucketEnd.isAfter(end)) {
                bucketEnd = end;
            }
            if (!bucketEnd.isAfter(cursor)) {
                break;
            }
            buckets.add(new BucketPlan(cursor, bucketEnd, label));
            cursor = bucketEnd;
        }
        return new TemperatureTrendPlan(window, buckets);
    }

    /**
     * 将温度样本按时间桶累积。
     */
    private void accumulateTemperatureByBucket(Map<BucketPlan, TemperatureAccumulator> accumulator,
                                               List<Map<String, Object>> rows) {
        if (CollectionUtils.isEmpty(rows) || CollectionUtils.isEmpty(accumulator)) {
            return;
        }
        for (Map<String, Object> row : rows) {
            long epochMillis = parseEpochMillis(row.get("time"));
            if (epochMillis < 0L) {
                continue;
            }
            for (Map.Entry<BucketPlan, TemperatureAccumulator> entry : accumulator.entrySet()) {
                BucketPlan bucket = entry.getKey();
                if (!bucket.contains(epochMillis)) {
                    continue;
                }
                TemperatureAccumulator bucketAccumulator = entry.getValue();
                for (int i = 0; i < TEMPERATURE_FIELDS.length; i++) {
                    String field = TEMPERATURE_FIELDS[i];
                    BigDecimal value = toBigDecimal(row.get(field));
                    bucketAccumulator.addValue(i, value);
                }
                break;
            }
        }
    }


    /**
     * 构建单条合并后的展示记录。
     */
    private Map<String, Object> buildCombinedRecord(Map<String, Object> switchRow,
                                                    Map<String, Object> hostRow,
                                                    String defaultDeviceId) {
        Map<String, Object> record = new LinkedHashMap<>(24);
        record.put("time", formatDisplayTime(switchRow.get("time")));
        record.put("deviceKgId", switchRow.get("device_id"));
        record.put("deviceId", hostRow != null && hostRow.get("device_id") != null
                ? hostRow.get("device_id")
                : defaultDeviceId);
        record.put("astatortemp1", hostRow == null ? null : hostRow.get("astatortemp1"));
        record.put("bstatortemp1", hostRow == null ? null : hostRow.get("bstatortemp1"));
        record.put("cstatortemp1", hostRow == null ? null : hostRow.get("cstatortemp1"));
        record.put("astatortemp2", hostRow == null ? null : hostRow.get("astatortemp2"));
        record.put("bstatortemp2", hostRow == null ? null : hostRow.get("bstatortemp2"));
        record.put("cstatortemp2", hostRow == null ? null : hostRow.get("cstatortemp2"));
        record.put("nonshaftendtemp", hostRow == null ? null : hostRow.get("nonshaftendtemp"));
        record.put("shaftendtemp", hostRow == null ? null : hostRow.get("shaftendtemp"));
        record.put("uab", switchRow.get("uab"));
        record.put("ubc", switchRow.get("ubc"));
        record.put("uca", switchRow.get("uca"));
        record.put("ia", switchRow.get("ia"));
        record.put("ib", switchRow.get("ib"));
        record.put("ic", switchRow.get("ic"));
        record.put("p", switchRow.get("p"));
        record.put("q", switchRow.get("q"));
        record.put("f", switchRow.get("f"));
        record.put("powerfactor", switchRow.get("powerfactor"));
        return record;
    }

    /**
     * 构建分页响应结果。
     */
    private Map<String, Object> buildCombinedPageResponse(String deviceId, String deviceKgId,
                                                          DeviceTimeScope scope, int pageNum, int pageSize,
                                                          long total, List<Map<String, Object>> records) {
        Map<String, Object> page = new LinkedHashMap<>(10);
        page.put("deviceId", deviceId);
        page.put("deviceKgId", deviceKgId);
        page.put("scope", scope.getCode());
        page.put("scopeName", scope.getDescription());
        page.put("pageNum", pageNum);
        page.put("pageSize", pageSize);
        page.put("total", total);
        long pageCount = pageSize <= 0 ? 0L : (long) Math.ceil(total / (double) pageSize);
        page.put("pageCount", pageCount);
        page.put("records", CollectionUtils.isEmpty(records) ? Collections.emptyList() : records);
        return page;
    }

    /**
     * 将毫秒时间戳转换为 IoTDB 可识别的时间字面量。
     */
    private String toTimestampLiteral(long epochMillis) {
        if (epochMillis <= 0L) {
            return null;
        }
        ZonedDateTime zonedDateTime = Instant.ofEpochMilli(epochMillis).atZone(ZoneId.systemDefault());
        return formatTimestampLiteral(zonedDateTime);
    }

    /**
     * 将任意时间值格式化为 yyyy-MM-dd HH:mm:ss.SSS 字符串，无法解析时返回原始对象字符串。
     */
    private String formatDisplayTime(Object value) {
        long epochMillis = parseEpochMillis(value);
        if (epochMillis > 0L) {
            ZonedDateTime zonedDateTime = Instant.ofEpochMilli(epochMillis).atZone(ZoneId.systemDefault());
            return SQL_TIMESTAMP_FORMATTER.format(zonedDateTime);
        }
        return value == null ? null : value.toString();
    }

    /**
     * 将温度值格式化为可展示文本。
     */
    private String formatTemperatureValue(BigDecimal value) {
        if (value == null) {
            return "-";
        }
        BigDecimal scaled = value.setScale(2, RoundingMode.HALF_UP);
        return scaled.stripTrailingZeros().toPlainString();
    }

    /**
     * 安全地将对象转换为 long 值，无法解析时返回 0。
     */
    private long toLong(Object value) {
        if (value == null) {
            return 0L;
        }
        if (value instanceof Number) {
            return ((Number) value).longValue();
        }
        if (value instanceof CharSequence) {
            String text = value.toString().trim();
            if (text.isEmpty()) {
                return 0L;
            }
            try {
                return Long.parseLong(text);
            } catch (NumberFormatException ignored) {
                try {
                    BigDecimal decimal = new BigDecimal(text);
                    return decimal.longValue();
                } catch (NumberFormatException ex) {
                    log.debug("无法解析计数值: {}", text, ex);
                }
            }
        }
        return 0L;
    }

    private LocalDateTime resolveScopeStart(DeviceTimeScope scope, ZoneId zone) {
        ZonedDateTime start = scope.rangeStart();
        if (!start.getZone().equals(zone)) {
            start = start.withZoneSameInstant(zone);
        }
        return start.toLocalDateTime();
    }

    private void validateDeviceId(String deviceId) {
        if (StringUtils.isBlank(deviceId)) {
            throw new ServiceException("设备编号不能为空");
        }
    }

    private TimeWindow buildTimeWindow(DeviceTimeScope scope) {
        ZoneId zone = ZoneId.systemDefault();
        ZonedDateTime end = ZonedDateTime.now(zone);
        ZonedDateTime start = scope.rangeStart();
        if (!start.getZone().equals(zone)) {
            start = start.withZoneSameInstant(zone);
        }
        return buildTimeWindow(start, end);
    }

    private TimeWindow buildTimeWindow(DeviceTimeScope scope,
                                       LocalDateTime startOverride,
                                       LocalDateTime endOverride) {
        if (startOverride == null && endOverride == null) {
            return buildTimeWindow(scope);
        }
        if (startOverride == null || endOverride == null) {
            throw new ServiceException("自定义时间范围需同时提供开始与结束时间");
        }
        ZoneId zone = ZoneId.systemDefault();
        ZonedDateTime start = startOverride.atZone(zone);
        ZonedDateTime end = endOverride.atZone(zone);
        return buildTimeWindow(start, end);
    }

    private TimeWindow buildTimeWindow(ZonedDateTime start, ZonedDateTime end) {
        ZoneId zone = ZoneId.systemDefault();
        if (!start.getZone().equals(zone)) {
            start = start.withZoneSameInstant(zone);
        }
        if (!end.getZone().equals(zone)) {
            end = end.withZoneSameInstant(zone);
        }
        if (!start.isBefore(end)) {
            throw new ServiceException("时间范围无效，请检查服务器时间配置");
        }
        return new TimeWindow(start, end);
    }

    private IotQueryResult query(String sql) {
        return queryService.executeQuery(sql);
    }

    private BigDecimal extractDecimal(IotQueryResult result, String key) {
        List<Map<String, Object>> rows = result.getRows();
        if (rows.isEmpty()) {
            return BigDecimal.ZERO;
        }
        BigDecimal value = toBigDecimal(rows.get(0).get(key));
        return value == null ? BigDecimal.ZERO : value;
    }

    private BigDecimal toBigDecimal(Object value) {
        if (value == null) {
            return null;
        }
        if (value instanceof BigDecimal) {
            return (BigDecimal) value;
        }
        if (value instanceof BigInteger) {
            return new BigDecimal((BigInteger) value);
        }
        if (value instanceof Number) {
            Number number = (Number) value;
            if (number instanceof Double || number instanceof Float) {
                return BigDecimal.valueOf(number.doubleValue());
            }
            return BigDecimal.valueOf(number.longValue());
        }
        if (value instanceof CharSequence) {
            String text = value.toString().trim();
            if (text.isEmpty()) {
                return null;
            }
            try {
                return new BigDecimal(text);
            } catch (NumberFormatException ex) {
                log.debug("Unable to parse numeric IoTDB value '{}'", text, ex);
            }
        }
        return null;
    }

    /**
     * 根据时间顺序的采样记录累积运行毫秒数：
     * - 遇到 isPowerOnPos = 1 视为开始计时（记录起点）；
     * - 遇到 isPowerOnPos = 0 且已记录起点时，累计本段(T_off - T_on)并清空起点；
     * - 若最后仍为运行态，则补齐至窗口结束。
     */
    private long accumulateRuntimeMillis(TimeWindow window, List<Map<String, Object>> rows) {
        long totalMillis = 0L;
        long powerOnStart = -1L;
        long windowStartMillis = window.getStartEpochMillis();
        long windowEndMillis = window.getEndEpochMillis();
        for (Map<String, Object> row : rows) {
            long timestamp = parseEpochMillis(row.get("time"));
            if (timestamp < 0) {
                continue;
            }
            Boolean powerOn = parsePowerOnFlag(row.get("isPowerOnPos"));
            if (powerOn == null) {
                continue;
            }
            if (powerOn) {
                if (powerOnStart < 0L) {
                    powerOnStart = timestamp;
                }
            } else {
                if (powerOnStart >= 0L && timestamp >= powerOnStart) {
                    totalMillis += (timestamp - powerOnStart);
                    powerOnStart = -1L;
                }
            }
        }
        if (powerOnStart >= 0L) {
            long effectiveStart = Math.max(powerOnStart, windowStartMillis);
            totalMillis += Math.max(0L, windowEndMillis - effectiveStart);
        }
        return totalMillis;
    }

    private void accumulateRuntimeByBucket(RuntimeTrendPlan plan,
                                           List<Map<String, Object>> rows,
                                           Map<BucketPlan, BigDecimal> accumulator) {
        if (CollectionUtils.isEmpty(rows)) {
            return;
        }
        long powerOnStart = -1L;
        TimeWindow window = plan.getQueryWindow();
        long windowEndMillis = window.getEndEpochMillis();
        for (Map<String, Object> row : rows) {
            long timestamp = parseEpochMillis(row.get("time"));
            if (timestamp < 0) {
                continue;
            }
            Boolean powerOn = parsePowerOnFlag(row.get("isPowerOnPos"));
            if (powerOn == null) {
                continue;
            }
            if (powerOn) {
                if (powerOnStart < 0L) {
                    powerOnStart = timestamp;
                }
            } else {
                if (powerOnStart >= 0L && timestamp >= powerOnStart) {
                    distributeRuntime(powerOnStart, timestamp, plan, accumulator);
                    powerOnStart = -1L;
                }
            }
        }
        if (powerOnStart >= 0L) {
            distributeRuntime(powerOnStart, windowEndMillis, plan, accumulator);
        }
    }

    private void distributeRuntime(long intervalStart,
                                   long intervalEnd,
                                   RuntimeTrendPlan plan,
                                   Map<BucketPlan, BigDecimal> accumulator) {
        if (intervalEnd <= intervalStart) {
            return;
        }
        for (BucketPlan bucket : plan.getBuckets()) {
            long overlapStart = Math.max(intervalStart, bucket.getStartEpochMillis());
            long overlapEnd = Math.min(intervalEnd, bucket.getEndEpochMillis());
            if (overlapEnd > overlapStart) {
                BigDecimal hours = BigDecimal.valueOf(overlapEnd - overlapStart)
                        .divide(BigDecimal.valueOf(MILLIS_PER_HOUR), ENERGY_SCALE, RoundingMode.HALF_UP);
                BigDecimal current = accumulator.get(bucket);
                if (current == null) {
                    current = ZERO_HOURS;
                }
                accumulator.put(bucket, current.add(hours));
            }
            if (bucket.getEndEpochMillis() >= intervalEnd) {
                break;
            }
        }
    }

    /**
     * 将 isPowerOnPos 的原始值解析为布尔语义：
     * - 支持 Boolean/数字/字符串，1 或 true 为开机，0 或 false 为关机；
     * - 其它取值返回 null，调用方据此忽略该采样点。
     */
    private Boolean parsePowerOnFlag(Object value) {
        if (value == null) {
            return null;
        }
        if (value instanceof Boolean) {
            return (Boolean) value;
        }
        if (value instanceof Number) {
            int numeric = ((Number) value).intValue();
            if (numeric == 1) {
                return Boolean.TRUE;
            }
            if (numeric == 0) {
                return Boolean.FALSE;
            }
            return null;
        }
        if (value instanceof CharSequence) {
            String text = value.toString().trim();
            if (text.isEmpty()) {
                return null;
            }
            if ("1".equals(text) || "true".equalsIgnoreCase(text)) {
                return Boolean.TRUE;
            }
            if ("0".equals(text) || "false".equalsIgnoreCase(text)) {
                return Boolean.FALSE;
            }
        }
        return null;
    }

    private long parseEpochMillis(Object value) {
        if (value instanceof Number) {
            return ((Number) value).longValue();
        }
        if (value instanceof Timestamp) {
            return ((Timestamp) value).getTime();
        }
        if (value instanceof CharSequence) {
            String text = value.toString().trim();
            if (!text.isEmpty()) {
                try {
                    return Long.parseLong(text);
                } catch (NumberFormatException ignored) {
                    try {
                        return Instant.parse(text).toEpochMilli();
                    } catch (DateTimeParseException ignoredIso) {
                        try {
                            ZonedDateTime zoned = ZonedDateTime.parse(text);
                            return zoned.toInstant().toEpochMilli();
                        } catch (DateTimeParseException ignoredZoned) {
                            try {
                                LocalDateTime localDateTime = LocalDateTime.parse(text, FLEXIBLE_LOCAL_DATE_TIME);
                                return localDateTime.atZone(ZoneId.systemDefault()).toInstant().toEpochMilli();
                            } catch (DateTimeParseException ex) {
                                log.debug("Unable to parse timestamp value '{}'", text, ex);
                            }
                        }
                    }
                }
            }
        }
        return -1L;
    }

    private static final class TemperatureTrendPlan {
        private final TimeWindow window;
        private final List<BucketPlan> buckets;

        private TemperatureTrendPlan(TimeWindow window, List<BucketPlan> buckets) {
            this.window = window;
            this.buckets = Collections.unmodifiableList(buckets);
        }

        boolean isEmpty() {
            return buckets.isEmpty();
        }

        TimeWindow getWindow() {
            return window;
        }

        Map<BucketPlan, TemperatureAccumulator> createAccumulator() {
            Map<BucketPlan, TemperatureAccumulator> map = new LinkedHashMap<>(buckets.size());
            for (BucketPlan bucket : buckets) {
                map.put(bucket, new TemperatureAccumulator());
            }
            return map;
        }

        List<Map<String, Object>> assemble(Map<BucketPlan, TemperatureAccumulator> accumulator) {
            List<Map<String, Object>> rows = new ArrayList<>(buckets.size());
            for (BucketPlan bucket : buckets) {
                TemperatureAccumulator acc = accumulator.get(bucket);
                Map<String, Object> row = new LinkedHashMap<>(4);
                row.put("label", bucket.getLabel());
                row.put("startTime", formatTimestampLiteral(bucket.getStart()));
                row.put("endTime", formatTimestampLiteral(bucket.getEnd()));
                row.put("values", acc == null ? Collections.emptyMap() : acc.toValueMap());
                rows.add(row);
            }
            return rows;
        }
    }

    private static final class RuntimeTrendPlan {
        private final TimeWindow queryWindow;
        private final String queryBinLiteral;
        private final List<BucketPlan> buckets;

        private RuntimeTrendPlan(TimeWindow queryWindow, String queryBinLiteral,
                                 List<BucketPlan> buckets) {
            this.queryWindow = queryWindow;
            this.queryBinLiteral = queryBinLiteral;
            this.buckets = Collections.unmodifiableList(new ArrayList<>(buckets));
        }

        TimeWindow getQueryWindow() {
            return queryWindow;
        }

        String getQueryBinLiteral() {
            return queryBinLiteral;
        }

        List<BucketPlan> getBuckets() {
            return buckets;
        }

        Map<BucketPlan, BigDecimal> createAccumulator() {
            Map<BucketPlan, BigDecimal> accumulator = new LinkedHashMap<>(buckets.size());
            for (BucketPlan bucket : buckets) {
                accumulator.put(bucket, ZERO_HOURS);
            }
            return accumulator;
        }

        void accumulate(Map<BucketPlan, BigDecimal> accumulator, long epochMillis, BigDecimal value) {
            for (BucketPlan bucket : buckets) {
                if (bucket.contains(epochMillis)) {
                    BigDecimal current = accumulator.get(bucket);
                    if (current == null) {
                        current = ZERO_HOURS;
                    }
                    accumulator.put(bucket, current.add(value));
                    break;
                }
            }
        }

        List<Map<String, Object>> assemble(Map<BucketPlan, BigDecimal> accumulator) {
            List<Map<String, Object>> results = new ArrayList<>(buckets.size());
            long nowMillis = System.currentTimeMillis();
            for (BucketPlan bucket : buckets) {
                BigDecimal runtime = accumulator.getOrDefault(bucket, ZERO_HOURS)
                        .setScale(ENERGY_SCALE, RoundingMode.HALF_UP);
                if (bucket.getStartEpochMillis() > nowMillis) {
                    runtime = ZERO_HOURS;
                }
                Map<String, Object> row = new LinkedHashMap<>(4);
                row.put("label", bucket.getLabel());
                row.put("startTime", formatTimestampLiteral(bucket.getStart()));
                row.put("endTime", formatTimestampLiteral(bucket.getEnd()));
                row.put("runtimeHours", runtime.setScale(2, RoundingMode.HALF_UP));
                results.add(row);
            }
            return results;
        }
    }

    private static final class HealthScoreTrendPlan {
        private final TimeWindow queryWindow;
        private final List<BucketPlan> buckets;

        private HealthScoreTrendPlan(TimeWindow queryWindow, List<BucketPlan> buckets) {
            this.queryWindow = queryWindow;
            this.buckets = Collections.unmodifiableList(new ArrayList<>(buckets));
        }

        TimeWindow getQueryWindow() {
            return queryWindow;
        }

        Map<BucketPlan, ScoreAccumulator> createAccumulator() {
            Map<BucketPlan, ScoreAccumulator> accumulator = new LinkedHashMap<>(buckets.size());
            for (BucketPlan bucket : buckets) {
                accumulator.put(bucket, new ScoreAccumulator());
            }
            return accumulator;
        }

        void accumulate(Map<BucketPlan, ScoreAccumulator> accumulator, long epochMillis, BigDecimal value) {
            for (BucketPlan bucket : buckets) {
                if (bucket.contains(epochMillis)) {
                    ScoreAccumulator scoreAccumulator = accumulator.get(bucket);
                    if (scoreAccumulator == null) {
                        scoreAccumulator = new ScoreAccumulator();
                        accumulator.put(bucket, scoreAccumulator);
                    }
                    scoreAccumulator.add(value);
                    break;
                }
            }
        }

        List<Map<String, Object>> assemble(Map<BucketPlan, ScoreAccumulator> accumulator) {
            List<Map<String, Object>> results = new ArrayList<>(buckets.size());
            for (BucketPlan bucket : buckets) {
                ScoreAccumulator scoreAccumulator = accumulator.get(bucket);
                BigDecimal average = scoreAccumulator != null ? scoreAccumulator.average() : ZERO_SCORE;
                Map<String, Object> row = new LinkedHashMap<>(4);
                row.put("label", bucket.getLabel());
                row.put("startTime", formatTimestampLiteral(bucket.getStart()));
                row.put("endTime", formatTimestampLiteral(bucket.getEnd()));
                row.put("scoreTotal", average);
                results.add(row);
            }
            return results;
        }
    }

    private static final class ScoreAccumulator {
        private BigDecimal sum = BigDecimal.ZERO;
        private int count;

        void add(BigDecimal value) {
            if (value == null) {
                return;
            }
            sum = sum.add(value);
            count++;
        }

        BigDecimal average() {
            if (count <= 0) {
                return ZERO_SCORE;
            }
            return sum.divide(BigDecimal.valueOf(count), SCORE_SCALE, RoundingMode.HALF_UP);
        }
    }

    private static final class TemperatureAccumulator {
        private final BigDecimal[] sums = new BigDecimal[TEMPERATURE_FIELDS.length];
        private final int[] counts = new int[TEMPERATURE_FIELDS.length];

        void addValue(int index, BigDecimal value) {
            if (value == null) {
                return;
            }
            sums[index] = sums[index] == null ? value : sums[index].add(value);
            counts[index]++;
        }

        Map<String, Object> toValueMap() {
            Map<String, Object> map = new LinkedHashMap<>(TEMPERATURE_FIELDS.length);
            for (int i = 0; i < TEMPERATURE_FIELDS.length; i++) {
                BigDecimal avg = null;
                if (counts[i] > 0 && sums[i] != null) {
                    avg = sums[i].divide(BigDecimal.valueOf(counts[i]), 2, RoundingMode.HALF_UP);
                }
                map.put(TEMPERATURE_FIELDS[i], avg);
            }
            return map;
        }
    }

    private static final class RowPoint {
        private final long epochMillis;
        private final Map<String, Object> row;

        private RowPoint(long epochMillis, Map<String, Object> row) {
            this.epochMillis = epochMillis;
            this.row = row;
        }
    }

    private static final class BucketPlan {
        private final ZonedDateTime start;
        private final ZonedDateTime end;
        private final String label;
        private final long startEpochMillis;
        private final long endEpochMillis;

        private BucketPlan(ZonedDateTime start, ZonedDateTime end, String label) {
            this.start = start;
            this.end = end;
            this.label = label;
            this.startEpochMillis = start.toInstant().toEpochMilli();
            this.endEpochMillis = end.toInstant().toEpochMilli();
        }

        ZonedDateTime getStart() {
            return start;
        }

        ZonedDateTime getEnd() {
            return end;
        }

        String getLabel() {
            return label;
        }

        boolean contains(long epochMillis) {
            return epochMillis >= startEpochMillis && epochMillis < endEpochMillis;
        }

        long getStartEpochMillis() {
            return startEpochMillis;
        }

        long getEndEpochMillis() {
            return endEpochMillis;
        }
    }

    private enum EnergyScope {
        TODAY("today", "本日") {
            @Override
            ZonedDateTime rangeStart(ZoneId zone, ZonedDateTime now) {
                return now.toLocalDate().atStartOfDay(zone);
            }
        },
        /* WEEK("week", "本周") {
             @Override
             ZonedDateTime rangeStart(ZoneId zone, ZonedDateTime now) {
                 ZonedDateTime monday = now.with(TemporalAdjusters.previousOrSame(DayOfWeek.MONDAY));
                 return monday.toLocalDate().atStartOfDay(zone);
             }
         },*/
        MONTH("month", "本月") {
            @Override
            ZonedDateTime rangeStart(ZoneId zone, ZonedDateTime now) {
                return now.toLocalDate().withDayOfMonth(1).atStartOfDay(zone);
            }
        },
        YEAR("year", "本年度") {
            @Override
            ZonedDateTime rangeStart(ZoneId zone, ZonedDateTime now) {
                return now.toLocalDate().with(TemporalAdjusters.firstDayOfYear()).atStartOfDay(zone);
            }
        };

        private final String code;
        private final String description;

        EnergyScope(String code, String description) {
            this.code = code;
            this.description = description;
        }

        static EnergyScope fromDeviceScope(DeviceTimeScope scope) {
            if (scope == null) {
                return MONTH;
            }
            switch (scope) {
                case TODAY:
                    return TODAY;
                case YEAR:
                    return YEAR;
                case MONTH:
                    return MONTH;
                default:
                    throw new ServiceException("能耗时间范围仅支持本日、本月、本年度");
            }
        }

        abstract ZonedDateTime rangeStart(ZoneId zone, ZonedDateTime now);

        String getCode() {
            return code;
        }

        String getDescription() {
            return description;
        }
    }

    private static final class TimeWindow {
        private final ZonedDateTime start;
        private final ZonedDateTime end;
        private final String startLiteral;
        private final String endLiteral;
        private final long startEpochMillis;
        private final long endEpochMillis;

        private TimeWindow(ZonedDateTime start, ZonedDateTime end) {
            this.start = start;
            this.end = end;
            this.startLiteral = formatTimestampLiteral(start);
            this.endLiteral = formatTimestampLiteral(end);
            this.startEpochMillis = start.toInstant().toEpochMilli();
            this.endEpochMillis = end.toInstant().toEpochMilli();
        }

        ZonedDateTime getStart() {
            return start;
        }

        ZonedDateTime getEnd() {
            return end;
        }

        String getStartLiteral() {
            return startLiteral;
        }

        String getEndLiteral() {
            return endLiteral;
        }

        long getStartEpochMillis() {
            return startEpochMillis;
        }

        long getEndEpochMillis() {
            return endEpochMillis;
        }
    }

    private static String formatTimestampLiteral(ZonedDateTime time) {
        return SQL_TIMESTAMP_FORMATTER.format(time);
    }

    private static String buildTemperatureStatsSelectClause() {
        StringBuilder builder = new StringBuilder();
        for (String field : TEMPERATURE_FIELDS) {
            if (builder.length() > 0) {
                builder.append(", ");
            }
            builder.append("MAX(").append(field).append(") AS ").append(field).append("_max, ");
            builder.append("MIN(").append(field).append(") AS ").append(field).append("_min, ");
            builder.append("AVG(").append(field).append(") AS ").append(field).append("_avg");
        }
        return builder.toString();
    }

    private static Map<String, String> buildFieldDescriptionMap() {
        Map<String, String> descriptionMap = new HashMap<>();
        loadSchemaDescriptions(descriptionMap, Product1714885465641558018DataSjn.class);
        loadSchemaDescriptions(descriptionMap, Product1714885465641558018DataJht.class);
        return Collections.unmodifiableMap(descriptionMap);
    }

    private static void loadSchemaDescriptions(Map<String, String> descriptionMap, Class<?> type) {
        Field[] fields = type.getDeclaredFields();
        for (Field field : fields) {
            Schema schema = field.getAnnotation(Schema.class);
            if (schema == null) {
                continue;
            }
            String description = schema.description();
            if (StringUtils.isBlank(description)) {
                continue;
            }
            String fieldName = field.getName();
            descriptionMap.putIfAbsent(fieldName, description);
            String snakeCase = toSnakeCase(fieldName);
            descriptionMap.putIfAbsent(snakeCase, description);
        }
    }

    private static Map<String, Field> buildFaultCountFieldMap() {
        Map<String, Field> map = new HashMap<>();
        for (Field field : DeviceFaultWindow.class.getDeclaredFields()) {
            if (field == null || Modifier.isStatic(field.getModifiers())) {
                continue;
            }
            if (!field.getType().equals(Integer.class)) {
                continue;
            }
            String name = field.getName();
            if (!name.toLowerCase(Locale.ROOT).endsWith("count")) {
                continue;
            }
            field.setAccessible(true);
            map.put(name, field);
        }
        return Collections.unmodifiableMap(map);
    }

    private static Map<String, String> buildFaultCountDescriptionMap() {
        Map<String, String> map = new TreeMap<>();
        for (Field field : DeviceFaultWindow.class.getDeclaredFields()) {
            if (!FAULT_COUNT_FIELD_MAP.containsKey(field.getName())) {
                continue;
            }
            Schema schema = field.getAnnotation(Schema.class);
            if (schema == null || StringUtils.isBlank(schema.description())) {
                continue;
            }
            map.put(field.getName(), schema.description());
        }
        return Collections.unmodifiableMap(map);
    }

    private static String resolveDescription(String fieldName) {
        if (StringUtils.isBlank(fieldName)) {
            return "";
        }
        String direct = FIELD_DESCRIPTION_MAP.get(fieldName);
        if (StringUtils.isNotBlank(direct)) {
            return direct;
        }
        String snakeCandidate = toSnakeCase(fieldName);
        return FIELD_DESCRIPTION_MAP.getOrDefault(snakeCandidate, "");
    }

    private static String toSnakeCase(String value) {
        if (StringUtils.isBlank(value)) {
            return value;
        }
        StringBuilder builder = new StringBuilder(value.length() + 4);
        for (int i = 0; i < value.length(); i++) {
            char c = value.charAt(i);
            if (Character.isUpperCase(c)) {
                if (builder.length() > 0) {
                    builder.append('_');
                }
                builder.append(Character.toLowerCase(c));
            } else {
                builder.append(c);
            }
        }
        return builder.toString();
    }
}
